import pandas as pd
from peewee import JOIN

from application.core.base_extract_time import BaseExtractTime
from application.db.mysql_db.info import ResourceInformationList, ResourceInformationTagsRelation, \
    ResourceInformationAttachmentList, ResourceInformationSectionList, ResourceSourceDict
from application.db.mysql_db.nsfc.NsfcInfoList import NsfcInfoList
from application.db.mysql_db.nsfc.NsfcResourceSourceDict import NsfcResourceSourceDict
from application.tasks.info_to_nsfc_task.data_storage_task import DataStorageTask
from application.tasks.info_to_nsfc_task.data_type_grouping_task import DataTypeGroupingTask


class InfoToNsfcExtract(BaseExtractTime):
    # 流程名称，用于日志或监控
    flow_name = "资讯湖到国自然基金资讯数据流"

    # 数据处理管道列表，按顺序执行
    pipeline_list = [
        DataTypeGroupingTask(),  # 数据类型分组任务
        DataStorageTask()  # 数据入库任务
    ]

    async def extract(self):
        """
        从资讯湖中提取信息，并对接国自然基金相关数据表。
        提取逻辑包括：
        1. 查询国自然基金已有信息列表
        2. 查询资讯湖的资源和主表信息
        3. 筛选未入库的信息
        4. 组装 sections 和 attachments
        5. 返回可供后续处理的数据框
        """

        # 查询字段列表，便于后续 select 使用
        nsfc_info_fields = [
            NsfcInfoList.information_id  # 国自然基金已有信息的 ID
        ]
        nsfc_resource_fields = [
            NsfcResourceSourceDict.source_id,
            NsfcResourceSourceDict.source_main_link  # 国自然基金资源表主链接
        ]

        info_list_fields = [
            ResourceInformationList.information_id,
            ResourceInformationList.source_id,
            ResourceInformationList.information_name,
            ResourceInformationList.information_description,
            ResourceInformationList.original_language,
            ResourceInformationList.cover,
            ResourceInformationList.original_link,
            ResourceInformationList.publish_date  # 主表字段，用于基础信息
        ]
        source_dict_fields = [
            ResourceSourceDict.source_id,
            ResourceSourceDict.source_main_link  # 资源来源字典字段
        ]
        info_section_fields = [
            ResourceInformationSectionList.section_id,
            ResourceInformationSectionList.information_id,
            ResourceInformationSectionList.section_order,
            ResourceInformationSectionList.section_attr,
            ResourceInformationSectionList.title_level,
            ResourceInformationSectionList.marc_code,
            ResourceInformationSectionList.src_text,
            ResourceInformationSectionList.dst_text,
            ResourceInformationSectionList.media_info  # 资讯章节相关字段
        ]
        info_tags_relation_fields = [
            ResourceInformationTagsRelation.information_id,
            ResourceInformationTagsRelation.tag_code,
            ResourceInformationTagsRelation.tag_value  # 标签关联字段
        ]

        info_attachment_fields = [
            ResourceInformationAttachmentList.attachment_id,
            ResourceInformationAttachmentList.information_id,
            ResourceInformationAttachmentList.attachment_name,
            ResourceInformationAttachmentList.attachment_address,
            ResourceInformationAttachmentList.display_order  # 附件字段
        ]

        # 查询国自然基金资源表，转换为 DataFrame
        nsfc_resource = pd.DataFrame(NsfcResourceSourceDict.select(*nsfc_resource_fields).dicts())
        # 查询资源来源字典，转换为 DataFrame
        source_dict = pd.DataFrame(ResourceSourceDict.select(*source_dict_fields).dicts())
        # 查询国自然基金已有信息列表
        nsfc_list = pd.DataFrame(NsfcInfoList.select(*nsfc_info_fields).dicts())

        # 将资讯湖资源与国自然基金资源进行合并
        merged_df = pd.merge(
            source_dict,
            nsfc_resource,
            on="source_main_link",  # 使用主链接作为合并键
            how="inner",  # 仅保留两者匹配的记录
            suffixes=("_info", "_nsfc")  # 避免 source_id 字段冲突
        )

        # 获取资讯湖中需要处理的 source_id 列表
        source_id_info_list = merged_df['source_id_info'].tolist() if not merged_df.empty else []
        # 获取国自然基金已存在的信息 ID 列表
        information_id_list = nsfc_list['information_id'].tolist() if not nsfc_list.empty else []

        # 查询资讯湖章节表，排除已存在国自然基金的信息
        info_section = pd.DataFrame(ResourceInformationSectionList.select(*info_section_fields).dicts().where(
            ~ResourceInformationSectionList.information_id.in_(information_id_list)
        ))

        # 查询资讯湖附件表，排除已存在国自然基金的信息
        info_attachment = pd.DataFrame(ResourceInformationAttachmentList.select(*info_attachment_fields).where(
            ~ResourceInformationAttachmentList.information_id.in_(information_id_list)
        ).dicts())

        # 按 information_id 分组，将章节表转换为字典列表，若为空则返回空字典
        section_list = (
            info_section.groupby('information_id', group_keys=False)
            .apply(lambda x: x.to_dict(orient='records'))
            if not info_section.empty else {}
        )

        # 按 information_id 分组，将附件表转换为字典列表，若为空则返回空字典
        attachment_list = (
            info_attachment.groupby('information_id', group_keys=False)
            .apply(lambda x: x.to_dict(orient='records'))
            if not info_attachment.empty else {}
        )

        # 查询资讯湖主表及标签关联表，左连接获取标签信息，排除已存在国自然基金的信息
        query = (
            ResourceInformationList
            .select(
                *info_list_fields,
                *info_tags_relation_fields,
            )
            .join(ResourceInformationTagsRelation, JOIN.LEFT_OUTER,
                  on=(ResourceInformationList.information_id == ResourceInformationTagsRelation.information_id)
                  )
            .where(
                ResourceInformationList.source_id.in_(source_id_info_list),
                ~ResourceInformationList.information_id.in_(information_id_list)  # 排除已存在信息
            )
            .dicts()
        )

        # 转换为 DataFrame
        info_list = pd.DataFrame(query)
        if info_list.empty:
            return info_list
        # 将章节和附件信息映射到主表中
        info_list['info_section'] = info_list['information_id'].map(section_list)
        info_list['info_attachment'] = info_list['information_id'].map(attachment_list)

        # 返回组装好的信息列表 DataFrame，用于后续管道处理
        return info_list
